package com.ygqh.baby.controller.utils;

import java.awt.FontMetrics;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.ygqh.baby.ao.DocumentType;

public class POIUtils {
	private final static Log logger = LogFactory.getLog(POIUtils.class);
	private final static int DPI_WINDOWS_NORMAL = 96;
	private final static int DPI_INDUSTRY_STANDARD = 72;
	
	public static String getCellValue(Cell cell) {
		if (cell == null)
			return "";
		FormulaEvaluator evaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
		String value = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: // 数字			
			Double d = cell.getNumericCellValue();
			if(d.doubleValue() == d.longValue()){
				value = String.valueOf(d.longValue());
			}else{
				value = String.valueOf(d);
			}			
			break;
		case Cell.CELL_TYPE_STRING: // 字符串
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_BOOLEAN: // Boolean
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			CellValue cellValue = evaluator.evaluate(cell);
			value = cellValue.getStringValue();
			break;
		case Cell.CELL_TYPE_BLANK: // 空值
			value = "";
			break;
		case Cell.CELL_TYPE_ERROR: // 故障
			value = null;
			break;
		default:
			value = null;
			break;
		}
		return value;
	}
		
	public static final Workbook createWorkbook(DocumentType docType, InputStream inputStream) throws Exception {
		if (docType.equals(DocumentType.xls)) {
			return new HSSFWorkbook(inputStream);
		} else if (docType.equals(DocumentType.xlsx)) {
			return new XSSFWorkbook(inputStream);
		} else {
			throw new IllegalArgumentException("不支持除：xls/xlsx以外的文件格式!!!");
		}
	}
	
	public static byte[] writeWorkbook(Workbook workbook) throws Exception{
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);
		baos.flush();
		byte[] aa = baos.toByteArray();
		return aa;
	}
	
	public static void writeWorkbook(Workbook workbook, ByteArrayOutputStream baos) throws Exception{		
		workbook.write(baos);
		baos.flush();
	}
	
	/**
	 * 获取字体的宽度（像素）
	 * @param workbook
	 * @return
	 */
	public static float getFontWidthInPixels(Font defFont){				
		return getFontWidthInPixels(defFont, "0");
	}
	
	/**
	 * 列宽转像素宽
	 * @param charWidth
	 * @param columnWidth
	 * @return
	 */
	public static double convertColumnWidth2Pixels(Font defFont, float columnWidth){							    
		double charNums = columnWidth / 256.0;
		//2位小数  
		charNums = new BigDecimal(charNums).setScale(2, BigDecimal.ROUND_HALF_UP).floatValue();  
		//总像素
		double pixel =  8 * charNums;
    	return pixel;
	}
	
	/**
	 * 获取字符串的像素宽度
	 * @param defFont
	 * @param str
	 * @return
	 */
	public static float getFontWidthInPixels(Font defFont, String str){				
		//cast workbook font to Java font
		java.awt.Font font = new java.awt.Font(defFont.getFontName(), java.awt.Font.PLAIN, defFont.getFontHeightInPoints());

	    //get font metrics
	    FontMetrics fontMetrics = sun.font.FontDesignMetrics.getMetrics(font);

	    //get char width
	    float fontWidth = fontMetrics.stringWidth(str);

	    fontWidth = fontWidth * DPI_WINDOWS_NORMAL / DPI_INDUSTRY_STANDARD; //Normally, Windows font is larger than industry standard

	    return fontWidth; 
	}
	
	/**
	 * 计算字体的高度
	 * @param defFont
	 * @return
	 */
	public static float getFontHeightInPixels(Font defFont){
		//cast workbook font to Java font
		java.awt.Font font = new java.awt.Font(defFont.getFontName(), java.awt.Font.PLAIN, defFont.getFontHeightInPoints());

		//get font metrics
		FontMetrics fontMetrics = sun.font.FontDesignMetrics.getMetrics(font);

		return (float)(fontMetrics.getMaxAscent() + fontMetrics.getMaxDescent() + 0.75) * DPI_WINDOWS_NORMAL / DPI_INDUSTRY_STANDARD;
    } 
	
	/**
	 * 获取行的像素高
	 * @param row
	 * @return
	 */
	public static float getRowHeightInPixels(Row row){       
        float height;
        if(row != null) height = row.getHeight();
        else height = row.getSheet().getDefaultRowHeight();

        return height/255*getFontHeightInPixels(row.getSheet().getWorkbook().getFontAt((short)0));
    } 
	
	/**  
     * 判断指定的单元格是否是合并单元格  
     * @param sheet   
     * @param row 行下标  
     * @param column 列下标  
     * @return  
     */
	public static boolean isMergedRegion(Cell cell, int cellNum) {
		Sheet sheet = cell.getRow().getSheet();
		Row row = cell.getRow();
		int sheetMergeCount = sheet.getNumMergedRegions();
		
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row.getRowNum() >= firstRow && row.getRowNum() <= lastRow) {
				if (cellNum >= firstColumn && cellNum <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}
	
	/**   
     * 获取合并单元格的值   
     * @param sheet   
     * @param row   
     * @param column   
     * @return   
     */
	public static String getMergedRegionValue(Cell cell, int cellNum) {
		Sheet sheet = cell.getRow().getSheet();
		Row row = cell.getRow();
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row.getRowNum() >= firstRow && row.getRowNum() <= lastRow) {
				if (cellNum >= firstColumn && cellNum <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}
		return null;
	}
	
	/**   
     * 获取合并单元格的宽度   
     * @param sheet   
     * @param row   
     * @param column   
     * @return   
     */   
	public static int getMergedRegionWidth(Cell cell, int cellNum, int unit) {
		Sheet sheet = cell.getRow().getSheet();
		Row row = cell.getRow();
		int sheetMergeCount = sheet.getNumMergedRegions();
		int totalWidth = 0;
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row.getRowNum() >= firstRow && row.getRowNum() <= lastRow) {
				if (cellNum >= firstColumn && cellNum <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					for(int j = firstColumn; j<= lastColumn; j++){
						Cell fCell = fRow.getCell(j);
						if(unit == 0){
							totalWidth += sheet.getColumnWidth(fCell.getColumnIndex());
						}else{
							totalWidth += sheet.getColumnWidthInPixels(fCell.getColumnIndex());
						}
					}					
				}
			}
		}
		return totalWidth;
	}
     	
	public static void autoRowHeight(Sheet sheet){		        
        for(int i = 0; i <= sheet.getLastRowNum(); i ++) {   
            Row row = sheet.getRow(i);  
            if(row == null) continue;            
            float fontHeight = 0;
            int nrowNum = 0;
            for(int j = 0; j <= row.getLastCellNum(); j ++) {
            	Cell cell = row.getCell(j);
            	if(cell == null) continue;
            	String text = getCellValue(row.getCell(j));
            	if(text != null){
            		text = text.trim();
            	}
            	if(StringUtils.isEmpty(text)){
            		continue;
            	}       
            	Font cellFont = sheet.getWorkbook().getFontAt(cell.getCellStyle().getFontIndex());//字体
            	float cellWidth = cell.getSheet().getColumnWidth(cell.getColumnIndex());
            	            	            	
            	Boolean isMergedRegion = isMergedRegion(cell, j);
            	if(isMergedRegion == true){
            		cellWidth = getMergedRegionWidth(cell, j,0);
            	}
            	
            	double colWidthInPixels = convertColumnWidth2Pixels(cellFont, (int)cellWidth);//列宽            	
            	float rowHeightInPixels = getRowHeightInPixels(row);
            	float fontHeightInPixels = getFontHeightInPixels(cellFont);//字符高度
            	float textWidth = getFontWidthInPixels(cellFont, text);//总字宽
            	int rowNum = 1, c_s = 0, halfCharWith = 4;    
            	            	
            	for(int c_i =1; c_i <= text.length(); c_i++){//判断行数             		  		
            		float tw = getFontWidthInPixels(cellFont, text.substring(c_s, c_i)) ; 
            		
            		if((tw >= (colWidthInPixels - halfCharWith) ) && text.length() > 1){
            			rowNum++;
            			c_s = c_i;            			
            		}
            	}
            	            	            	
            	logger.info(rowNum+"列像素宽度："+colWidthInPixels+",文字宽度："+textWidth+","+i+"-"+j+">"+text);
            	rowNum--;
                if (rowNum > 0 && rowNum > nrowNum) {
                	nrowNum = rowNum; //新增行 = 总行数 - 1,
                	fontHeight = fontHeightInPixels;
                }                
            }  
            if(nrowNum == 0) continue;
            //设置新的行高
            int rowHeight =  row.getHeight();
            rowHeight += nrowNum * fontHeight * DPI_INDUSTRY_STANDARD / DPI_WINDOWS_NORMAL * 20;
            row.setHeight((short)rowHeight);  
        }  
	}
}
